package com.easyexcell.easyexcellexample.service;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.easyexcell.easyexcellexample.dto.QuotationRecordDetailedExportDto;
import com.easyexcell.easyexcellexample.utils.esayexcell.EasyExcelUtil;
import com.easyexcell.easyexcellexample.utils.esayexcell.bo.QuotationRecordDetailedExportBo_Test;
import com.easyexcell.easyexcellexample.utils.esayexcell.dto.QuoteItemImportDto;
import com.easyexcell.easyexcellexample.utils.esayexcell.handler.CustomCellWriteHandler;
import com.easyexcell.easyexcellexample.utils.esayexcell.handler.LockSheetWriteHandler;
import com.easyexcell.easyexcellexample.utils.esayexcell.handler.UnLockSheetWriteHandler;
import com.easyexcell.easyexcellexample.utils.esayexcell.listen.QuoteImportListener;
import com.easyexcell.easyexcellexample.utils.esayexcell.vo.QuotationRecordDetailedVo;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @User c.c
 * @Since 2023/9/2
 * @Time 18:04
 * @Description
 */
@Service
public class EasyExcellExportService {


  public List<QuotationRecordDetailedVo> importQuoteDetailExcell(MultipartFile file) {
    QuoteImportListener<QuotationRecordDetailedVo> quotationRecordDetailedVoQuoteImportListener = new QuoteImportListener<>();
    EasyExcelUtil.importExcell(file, QuoteItemImportDto.class, quotationRecordDetailedVoQuoteImportListener, 2);
    return quotationRecordDetailedVoQuoteImportListener.getQuotationRecordDetailedVos();
  }

  public void exportExcell(List<QuotationRecordDetailedExportDto> quotationRecordDetailedExportDtos, HttpServletResponse response) {
    InputStream inputStream = null;
    ExcelWriter excelWriter = null;

//    CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(3, 4, 5, 7);
//    String[] strings = {"1", "2", "3"};
//    CustomSheetWriteHandler customSheetWriteHandler = new CustomSheetWriteHandler(cellRangeAddressList, strings, true);

    try {
      //2.模板输出
      ClassPathResource cpr = new ClassPathResource("templates/" + "报价明细导出模板.xlsx");
      inputStream = cpr.getInputStream();
      //3. 数据写出
      excelWriter = EasyExcel.write(response.getOutputStream(), QuotationRecordDetailedExportBo_Test.class)
        .withTemplate(inputStream)
        .build();
    } catch (IOException e) {
      throw new RuntimeException(e);
    }
    //3.1 操作第一个sheet(记得注册自定义的CellWriteHandler)
    WriteSheet writeSheet = EasyExcel.writerSheet()
      //设置公式
      .registerWriteHandler(new CustomCellWriteHandler())
      //锁全局sheet
      .registerWriteHandler(new LockSheetWriteHandler())
      //设置哪些单元格解锁
      .registerWriteHandler(new UnLockSheetWriteHandler())
      .build();

    // 3.2 填充列表数据
    excelWriter.fill(quotationRecordDetailedExportDtos, FillConfig.builder().forceNewRow(Boolean.TRUE).build(), writeSheet).finish();
    // 设置强制计算公式，不然公式会以字符串的形式显示在excel中
    Workbook workbook = excelWriter.writeContext().writeWorkbookHolder().getWorkbook();
    workbook.setForceFormulaRecalculation(true);
    excelWriter.close();

  }
}
